package org.wwy.poitl;

import java.io.*;
import java.util.Date;
 
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
 
public class TestExcelWriter {
    public static void main(String[] args) throws IOException {
        BufferedOutputStream os = null;
        long a = System.currentTimeMillis();
        Workbook wb = new SXSSFWorkbook();
        Workbook wb2 = new XSSFWorkbook();
        try
        {
            CellStyle cellStyle = wb.createCellStyle();
//            cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //下边框
//            cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
//            cellStyle.setBorderTop(CellStyle.BORDER_THIN);//上边框
//            cellStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框
            
            Font font = wb.createFont();    
            font.setFontName("微软雅黑");    
            font.setFontHeightInPoints((short) 10);//设置字体大小
            cellStyle.setFont(font);

            //设置字体居中
//            cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            
            //第一行标题
            Sheet sheet = wb.createSheet();
            Row row0 = sheet.createRow(0);
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,2));
            Cell parkNameCell = row0.createCell(0);
            parkNameCell.setCellStyle(cellStyle);
            parkNameCell.setCellValue("百万行数据写入");
 
            Row row2 = sheet.createRow(1);
            for(int cellnum = 0; cellnum < 3; cellnum++){
                Cell cell = row2.createCell(cellnum);
                cell.setCellStyle(cellStyle);
                if (cellnum == 0) {
                    cell.setCellValue("序号");
                } else if (cellnum == 1) {
                    cell.setCellValue("随机数");
                    RichTextString richStringCellValue = new XSSFRichTextString(cell.getStringCellValue());
                    Font font2 = new XSSFFont();
                    font2.setColor(Font.COLOR_RED);
                    richStringCellValue.applyFont(0, 2, font2);
                    cell.setCellValue(richStringCellValue);
                } else if (cellnum == 2) {
                    cell.setCellValue("时间");
                }
            }
//            wb = new SXSSFWorkbook((XSSFWorkbook) wb);
            int sn = 1;
            String property = "";
            int startRow = 2;
            for (int i = 0; i < 100; i++)
            {
                Row row = sheet.createRow(startRow);
                for(int cellnum = 0; cellnum < 3; cellnum++){
                    Cell cell = row.createCell(cellnum);
                    cell.setCellStyle(cellStyle);
                    if (cellnum == 0) {
                        cell.setCellValue(sn);
                    } else if (cellnum == 1) {
                        property = String.valueOf(Math.random());
                        cell.setCellValue(property);

                    } else if (cellnum == 2) {
                        property = DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss");
                        cell.setCellValue(property);

                    } 
                }
                sn++;
                startRow++;
            }
            Row totalRow = sheet.createRow(startRow);
            Cell cell = totalRow.createCell(0);
            cell.setCellValue("统计所需时间(s):");
            cell.setCellStyle(cellStyle);
            cell = totalRow.createCell(1);
            cell.setCellStyle(cellStyle);
            cell = totalRow.createCell(2);
            cell.setCellStyle(cellStyle);
            cell.setCellValue((System.currentTimeMillis() - a)/1000.0);
            String tempPath ="E:\\2.xlsx";
            os = new BufferedOutputStream(new FileOutputStream(tempPath));
            wb.write(os);

            System.out.println("所需时间(s)："+(System.currentTimeMillis() - a));
        }

        finally
        {
            if(os!=null){
                os.close();
            }
            IOUtils.closeQuietly(os);
        }
    }
}